from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import  ForeignKey
from datetime import datetime
db = SQLAlchemy()


user_role = db.Table(
    'user_role',
    db.Column('user_id', db.ForeignKey('user.id'), primary_key=True),
    db.Column('role_id', db.ForeignKey('role.id'), primary_key=True)
)


# 用户
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    login_time = db.Column(db.DateTime, default=datetime.now)
    name = db.Column(db.String(16))
    pwd = db.Column(db.String(50))
    phone = db.Column(db.String(11))
    img = db.Column(db.String(1000),
                    default='https://tse4-mm.cn.bing.net/th/id/OIP-C.zP5XTRMsotw4v3aFj3fpQQAAAA?pid=ImgDet&rs=1')
    money = db.Column(db.DECIMAL(8, 2), default=0)
    shop = db.relationship("Shop", backref='user')
    check = db.relationship('CheckIn', uselist=False, backref='user')
    role_id = db.relationship("Role", secondary=user_role, backref='user')


# 角色表
class Role(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16))
    # power_id = db.Column(db.Integer, db.ForeignKey('power.id'))


power_role = db.Table(
    'power_role',
    db.Column('power_id', db.ForeignKey('power.id'), primary_key=True),
    db.Column('role_id', db.ForeignKey('role.id'), primary_key=True)
)


# 权限表
class Power(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16))
    role = db.relationship("Role", secondary=power_role, backref=db.backref('power'))
    url = db.Column(db.String(1024))
    method = db.Column(db.String(16))


class CheckIn(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    status = db.Column(db.Boolean, default=0)
    check_date = db.Column(db.DATE)


# 类别表
class Cate(db.Model):
    __tablename__ = 'cate'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="ID")
    cname = db.Column(db.String(68),  nullable=False, comment="名字")
    shops_id = db.Column(ForeignKey('shops.id', ondelete='CASCADE'), nullable=False)


# 订单表
class Order(db.Model):
    __tablename__ = 'order'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="ID")
    address = db.Column(db.String(68),  nullable=False, comment="地址")
    state = db.Column(
                    db.Enum(
                        '未完成',
                        '已完成',
                    ), server_default='未完成',nullable=False)
    price = db.Column(db.DECIMAL(12.2), nullable=False, comment="总价")
    number = db.Column(db.String(68), nullable=False, comment="商品数量")
    remark = db.Column(db.String(68), nullable=False, comment="备注")
    user_id = db.Column(ForeignKey('user.id', ondelete='CASCADE'), nullable=False)


# 购物车
class Shoping(db.Model):
    __tablename__ = 'shoping'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="ID")
    price = db.Column(db.DECIMAL(12.2),nullable=False, comment="总价")
    number = db.Column(db.String(68), nullable=False, comment="数量")
    user_id = db.Column(ForeignKey('user.id',ondelete='CASCADE'), nullable=False)
    product_id = db.Column(ForeignKey('product.id',ondelete='CASCADE'), nullable=False)


# 商品表
class Shop(db.Model):
    __tablename__ = 'shop'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="ID")
    sh_name = db.Column(db.String(68), nullable=False, comment="商品名")
    price = db.Column(db.DECIMAL(12.2), nullable=False, comment="单价")
    repertory = db.Column(db.String(68),  nullable=False, comment="库存")
    img = db.Column(db.String(266),  nullable=False, comment="图片")
    referral = db.Column(db.String(68), nullable=False, comment="介绍")
    cate_id = db.Column(ForeignKey('cate.id',ondelete='CASCADE'), nullable=False)
    shops_id = db.Column(ForeignKey('shops.id',ondelete='CASCADE'), nullable=False)



# 成品表
class product(db.Model):
    __tablename__ = 'product'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="ID")
    shop_id = db.Column(ForeignKey('shop.id', ondelete='CASCADE'), nullable=False)
    mate_id = db.Column(ForeignKey('mate.id', ondelete='CASCADE'), nullable=False)
    order_id = db.Column(ForeignKey('order.id', ondelete='CASCADE'), nullable=False)


shop_table = db.Table(
    'shop_table',
    db.Column('mate_id',db.ForeignKey("mate.id"), primary_key=True),
    db.Column('product_id',db.ForeignKey("product.id"), primary_key=True),
)


smast_table = db.Table(
    'smast_table',
    db.Column("shop_id", db.ForeignKey('shop.id'), primary_key=True),
    db.Column('mate_id', db.ForeignKey("mate.id"), primary_key=True),
)


# 小料表
class Material(db.Model):
    __tablename__ = 'mate'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="ID")
    mate_name = db.Column(db.String(68), nullable=False, comment="商品名")
    price = db.Column(db.DECIMAL(12.2),  nullable=False, comment="单价")

goods_business = db.Table(
    'goods_business',
    db.Column('good_id', db.ForeignKey('goods.id'), primary_key=True),
    db.Column('business_id', db.ForeignKey('business.id'), primary_key=True),
)

# 商家表
class Shops(db.Model):
    __tablename__ = 'shops'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="ID")
    shops_name = db.Column(db.String(68),  nullable=False, comment="商家名")
    address = db.Column(db.String(68), nullable=False, comment="商家地址")
    state = db.Column(
        db.Enum(
            '正在营业',
            '休息',
        ), server_default='正在营业', nullable=False)
    mobile = db.Column(db.Integer, nullable=False, comment="商家电话")


